using System;
using System.Collections.Generic;
using System.Text;

using student_factory.Metier;

using System.Data;
using System.Data.SqlClient;

namespace student_factory.DAO {
    class ProjetDAO {

        private static String connection = "data source=localhost;initial catalog=STUDENTS_FACTORY;User ID=sa;Password=";

        public static IList<Projet> getActiveProjects()
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Projet> projets = new List<Projet>();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT * from PROJETS";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    Projet projet = new Projet();
                    projet.Id_p = reader[ "ID_P" ].ToString();
                    projet.Id_c = reader[ "ID_C" ].ToString();
                    projet.Id_u = reader[ "ID_U" ].ToString();
                    projet.Prj_nom = reader["PRJ_NOM"].ToString();
                    projet.Prj_description = reader[ "PRJ_DESCRIPTION" ].ToString();
                    projets.Add(projet);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return projets;
        }

        public static IList<Projet> getNotFilledProj()
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Projet> projets = new List<Projet>();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT p.ID_P, PRJ_NOM, PRJ_DESCRIPTION FROM ETUDIANTS as e RIGHT JOIN PROJETS as p ON p.ID_P = e.ID_P LEFT JOIN CONTENU_PROJET as c ON p.ID_P = c.ID_P GROUP BY p.ID_P, PRJ_NOM, CPR_QUANTITE, PRJ_DESCRIPTION HAVING COUNT(ETU_NOM) < CPR_QUANTITE";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    Projet projet = new Projet();
                    projet.Id_p = reader["ID_P"].ToString();
                    projet.Prj_nom = reader["PRJ_NOM"].ToString();
                    projet.Prj_description = reader["PRJ_DESCRIPTION"].ToString();
                    projets.Add(projet);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return projets;
        }

        public static Projet getProjectByName(string name)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            Projet projet = new Projet();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT * from PROJETS WHERE PRJ_NOM like '" + name + "'";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {

                    projet.Id_p = reader["ID_P"].ToString();
                    projet.Id_c = reader["ID_C"].ToString();
                    projet.Id_u = reader["ID_U"].ToString();
                    projet.Prj_nom = reader["PRJ_NOM"].ToString();
                    projet.Prj_description = reader["PRJ_DESCRIPTION"].ToString();
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return projet;
        }

        public static bool addProjet(string id_c, string id_u, string nom, string description, out string id_p)
        {
            SqlConnection connexion = null;
            bool retour = true;
            id_p = "error";
            try
            {
                connexion = new SqlConnection();
                SqlDataReader reader = null;
                connexion.ConnectionString = connection;
                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "INSERT INTO PROJETS ( ID_C , ID_U , PRJ_NOM, PRJ_DESCRIPTION )" +
                   "VALUES ( '" + id_c + "' , '" + id_u + "' , '" + nom + "' , '" + description + "' )";

                int modif = commande.ExecuteNonQuery();

                commande.CommandText = "SELECT ID_P FROM PROJETS WHERE PRJ_NOM LIKE '" + nom + "' AND PRJ_DESCRIPTION LIKE '" + description + "' ";

                reader = commande.ExecuteReader();
                while (reader.Read())
                {
                    id_p = reader["ID_P"].ToString();
                }

                if (modif == 0)
                    retour = false;

            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (connexion != null)
                    connexion.Close();
            }

            return retour;
        }

        public static bool addContenuProjet(string id_p, string id_i, int quantite)
        {
            SqlConnection connexion = null;
            bool retour = true;
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;
                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                Console.WriteLine(
                    commande.CommandText = "INSERT INTO CONTENU_PROJET ( ID_P , ID_I , CPR_QUANTITE )" +
                   "VALUES ( '" + id_p + "' , '" + id_i + "' , '" + quantite + "' )");

                int modif = commande.ExecuteNonQuery();

                
                if (modif == 0)
                    retour = false;

            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (connexion != null)
                    connexion.Close();
            }

            return retour;
        }
    }
}
